package databases;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import settings.Settings;
import utilities.ANSI;
import utilities.Console;

/**
 * The Class MySQL.
 * 
 * @author Nils Berlijn
 * @author Henderikus Harms
 * @version 1.0
 * @since June 1, 2014
 */
public final class MySQL 
{
	/** The connection. */
	private static Connection connection;
	
	/** The console. */
	private static Console console;
	
	/**
	 * Connect.
	 */
	public static void connect()
	{
		try 
		{
		    Class.forName(Settings.MYSQL_DRIVER);
		    
		    connection = DriverManager.getConnection(Settings.MYSQL_URL + Settings.MYSQL_DATABASE, Settings.MYSQL_USER, Settings.MYSQL_PASSWORD);
		    
			System.out.println(ANSI.GREEN + "MySQL connection succesfull established." + ANSI.NORMAL);	
			System.out.println(ANSI.YELLOW + "MySQL is waiting for incoming queries...\n" + ANSI.NORMAL);	
	    }
	    catch(Exception e)
	    {
			System.out.println(ANSI.RED + "MySQL connection failed by trying to establishing a new connection.\n" + ANSI.NORMAL);	
	    }		
	}
	
	/**
	 * Console.
	 *
	 * @param console the console
	 */
	public static void console(Console console)
	{
		MySQL.console = console;
	}	
	
	/**
	 * Prints the result set.
	 *
	 * @param resultSet the result set
	 * @throws SQLException the SQL exception
	 */
	public static void printResultSet(ResultSet resultSet) throws SQLException
	{
		console.write(ANSI.YELLOW + " _________________________\n");
		console.write(" Stn:\t\t" + resultSet.getInt("stn"));	
		console.write(" Name:\t\t" + resultSet.getString("name"));	
		console.write(" Country:\t" + resultSet.getString("country"));	
		console.write(" Timezone:\t" + resultSet.getInt("timezone"));	
		console.write(" Latitude:\t" + resultSet.getDouble("latitude"));
		console.write(" Longitude:\t" + resultSet.getDouble("longitude"));
		console.write(" Elevation:\t" + resultSet.getDouble("elevation"));
		console.write(" _________________________" + ANSI.NORMAL);		
	}
	
	/**
	 * Select weather station by stn.
	 *
	 * @param stn the stn
	 */
	public static void selectWeatherStationByStn(int stn)
	{
		try
		{
			Statement statement = connection.createStatement();
			String query= ("SELECT * FROM stations WHERE stn = " + stn + ";");
			ResultSet resultSet = statement.executeQuery(query);	
			
			while(resultSet.next())
			{
				printResultSet(resultSet);
			}
		}
		catch(SQLException e)
		{

		}
	}
	
	/**
	 * Select weather station by country.
	 *
	 * @param country the country
	 * @param amount the amount
	 */
	public static void selectWeatherStationByCountry(String country, int amount)
	{
		try
		{
			Statement statement = connection.createStatement();
			String query= ("SELECT * FROM stations WHERE country = \"" + country + "\" LIMIT 0, " + amount + ";");
			ResultSet resultSet = statement.executeQuery(query);	
			
			while(resultSet.next())
			{
				printResultSet(resultSet);				
			}
		}
		catch(SQLException e)
		{

		}
	}
	
	/**
	 * Select weather station stn by country.
	 *
	 * @param country the country
	 * @param amount the amount
	 * @return the result set
	 * @throws SQLException the SQL exception
	 */
	public static ResultSet selectWeatherStationStnByCountry(String country, int amount) throws SQLException
	{
		Statement statement = connection.createStatement();
		String query= ("SELECT * FROM stations WHERE country = \"" + country + "\" LIMIT 0, " + amount + ";");
		ResultSet resultSet = statement.executeQuery(query);	
		
		return resultSet;
	}	
}